package cn.fu.excle;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel���ݴ�����
 * @author Foldcc
 *
 */
public class ExcelUtils {
	/**
	 * @param table
	 * @param filePath
	 * @return
	 */
	public boolean toExcel(List<String[]> table , String filePath){
//		FileUtils.createFile(filePath);
		XSSFWorkbook  wb = new XSSFWorkbook ();
        SXSSFWorkbook swb=new SXSSFWorkbook(wb,1000);
		
        SXSSFSheet sheet = (SXSSFSheet) swb.createSheet("Sheet");
        
        for (int i = 0 ; i < table.size() ; i++) {
        	Row row = sheet.createRow(i);
        	for (int j = 0 ; j < table.get(i).length ; j++) {
				row.createCell(j).setCellValue(table.get(i)[j]);
			}
		}
        FileOutputStream fileOut;
		try {
			if(new File(filePath).exists()) {
				new File(filePath).mkdirs();
			}
			fileOut = new FileOutputStream(filePath);
			
	        swb.write(fileOut);
	        
	        swb.close();
	        fileOut.close();
	        
	        return true;
		
		} catch (FileNotFoundException e) {
			System.out.println("文件没有找到");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * @param rs
	 * @param filePath
	 * @return
	 */
	public boolean toExcelWithResultSet(ResultSet rs, String filePath){
		if(rs == null){
			System.out.println("rsΪ��");
			return false;
		}
		try {
			rs.last();
			//��ȡ�ж���������
			long rcount = rs.getRow();
			
			if(rcount > 1000000){
				System.out.println("��ExcelUtils�� ����������");
				return false;
			}
			
			rs.first();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		XSSFWorkbook  wb = new XSSFWorkbook ();
        SXSSFWorkbook swb=new SXSSFWorkbook(wb,10000);
        SXSSFSheet sheet = (SXSSFSheet) swb.createSheet("Sheet");
        ResultSetMetaData data;
        try {
        	
			data = rs.getMetaData();
			int count = data.getColumnCount();
			Row row;
			row = sheet.createRow(0);
			for (int i = 1; i <= data.getColumnCount() ; i++){
				row.createCell(i-1).setCellValue(data.getColumnName(i));
			}
			int c = 1;
			while(rs.next()){
				row = sheet.createRow(c);
				for (int i = 1; i <= count; i++) {
					row.createCell(i-1).setCellValue(rs.getString(i));
				}
				c++;
			}
			System.out.println("��������: " + c);
			FileOutputStream fileOut;
			fileOut = new FileOutputStream(filePath);
			// �Ѵ����Ĺ�����������ļ���
	        swb.write(fileOut);
			swb.close();
			 //�ر������
	        fileOut.close();
			return true;
		} catch (SQLException e) {
			
			e.printStackTrace();
			System.out.println();
			System.out.println("��ExcelUtils�� ������ȡʧ��");
		} catch (Exception e) {
			
			e.printStackTrace();
			System.out.println("��ExcelUtils�� ����Excelʧ��");
		}
		return false;
	}
	
	/**
	 * �ж��ļ��Ƿ�Ϊexcel�ļ�
	 * @param file
	 * @return
	 */
	public boolean checkFile(File file){  
        //�ж��ļ��Ƿ����  
        if(null == file){
        	return false;
        }
        //����ļ���  
        String fileName = file.getName();
        //�ж��ļ��Ƿ���excel�ļ�  
        if(fileName.endsWith("xlsx")){  
        	return true;
        }
        return false;
	}
	
	/**
	 * ��ȡָ��·���µ�exlce�ļ�
	 * @param file
	 * @return
	 */
	public List<String[]> readExcel(File file){
		List<String[]> list = new ArrayList<String[]>();
        try {  
            InputStream inputStream = new FileInputStream(file);  
            String fileName = file.getName();  
            Workbook wb = null;  
            if(fileName.endsWith("xls")){  
                wb = new HSSFWorkbook(inputStream);//����xls��ʽ  
            }else if(fileName.endsWith("xlsx")){  
                wb = new XSSFWorkbook(inputStream);//����xlsx��ʽ  
            }
            Sheet sheet = wb.getSheetAt(0);//��һ��������  
             
            int firstRowIndex = sheet.getFirstRowNum();  
            int lastRowIndex = sheet.getLastRowNum();  
            for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex ++){  
                Row row = sheet.getRow(rIndex);  
                if(row != null){  
                    int firstCellIndex = row.getFirstCellNum();  
                    int lastCellIndex = row.getLastCellNum();
                    String[] tempCell = new String[lastCellIndex];
                    for(int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex ++){  
                        Cell cell = row.getCell(cIndex);  
                        if(cell != null){
                        	tempCell[cIndex] = cell.toString();
                        }
                    }
                    list.add(tempCell);
                }  
            }  
        } catch (FileNotFoundException e) {  
        	
            e.printStackTrace();  
            return null;
        } catch (IOException e) {  
            e.printStackTrace();
            return null;
        }
        return list;
    }
	
	/**
	 * ��ȡָ����������� ��������
	 * @param file
	 * @return
	 */
	public String[] readExcelInfo(File file){
		String[] info = new String[2];
        try {  
            InputStream inputStream = new FileInputStream(file);  
            String fileName = file.getName();  
            Workbook wb = null;  
            if(fileName.endsWith("xls")){
                wb = new HSSFWorkbook(inputStream);//����xls��ʽ 
            }else if(fileName.endsWith("xlsx")){
                wb = new XSSFWorkbook(inputStream);//����xlsx��ʽ 
            }
            Sheet sheet = wb.getSheetAt(0);//��һ��������  
            int lastRowIndex = sheet.getLastRowNum(); 
            Row hssfRow = sheet.getRow(0);
            if(hssfRow != null){
            	info[0] = (lastRowIndex + 1 )+ "";
                info[1] = hssfRow.getPhysicalNumberOfCells() + "";
            }else{
            	info[0] = "0";
            	info[1] = "0";
            }
            wb.close();
            inputStream.close();
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
            return null;
        } catch (IOException e) {  
            e.printStackTrace();
            return null;
        }
        return info;
    }
	
	/**
	 * �Ƚ�����excel�ļ������Ƿ���ͬ
	 * @param oldExcel
	 * @param nowExcel
	 * @return
	 */
	public String excelCompare(File oldExcel , File nowExcel){
		String log = null;
		InputStream inputStream;
		InputStream inputStream2;
		try {
			inputStream = new FileInputStream(oldExcel);
			inputStream2 = new FileInputStream(nowExcel);
			Workbook wb = new XSSFWorkbook(inputStream);//����xlsx��ʽ  
			Workbook wb2 = new XSSFWorkbook(inputStream2);//����xlsx��ʽ  
			Sheet sheet = wb.getSheetAt(0);//��һ��������  
			Sheet sheet2 = wb2.getSheetAt(0);//��һ��������  
			int firstRowIndex = sheet.getFirstRowNum();  
            int lastRowIndex = sheet.getLastRowNum();  
            for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex ++){  
                Row row = sheet.getRow(rIndex);  
                Row row2 = sheet2.getRow(rIndex);  
                if(row != null && row2 != null){  
                    int firstCellIndex = row.getFirstCellNum();  
                    int lastCellIndex = row.getLastCellNum();
                    for(int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex ++){  
                        Cell cell = row.getCell(cIndex);  
                        Cell cell2 = row2.getCell(cIndex);
                        if(cell != null && cell2 != null){
                        	if(!cell.toString().equals(cell2.toString())){
                        		log = "�������б��޸ģ� " + (rIndex+1) + "��, " + (cIndex+1) +" �� ���ֲ�ͬ";
                        		wb.close();
                    			wb2.close();
                        		return log;
                        	}
                        }else{
                        	log = "�������б��޸ģ� ��" + rIndex + "�з��ֲ�ͬ";
                        	wb.close();
                			wb2.close();
                    		return log;
                        }
                    }
                }else{
                	log = "�������б��޸ģ� ��" + rIndex + "�з��ֲ�ͬ";
                }
            }
			wb.close();
			wb2.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return log;
	}
	
	/**
	 * ��ָ��excel�ļ�β���������
	 * @param msg
	 * @param file
	 * @return
	 */
	public boolean addMsgToExcel(List<String[]> msg , File file){
		try {
			InputStream inputStream = new FileInputStream(file);
			Workbook wb = new XSSFWorkbook(inputStream);//����xlsx��ʽ 
			Sheet sheet = wb.getSheetAt(0);//��һ��������  
			int endcount = sheet.getLastRowNum();
			for (String[] strings : msg) {
				Row row = sheet.createRow(endcount++);
				if(row != null){
					for (int i = 0; i < strings.length; i++) {
						row.createCell(i).setCellValue(strings[i]);
					}
				}
			}
			FileOutputStream fileOut;
			fileOut = new FileOutputStream(file.getPath());
			// �Ѵ����Ĺ�����������ļ���
	       
			wb.close();
//			 �ر������
	        fileOut.close();
			return true;
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}
